function BookmarksService() { }

BookmarksService.prototype = {
    
    DEFAULT_VALUES: {//id: 0,
            status: 0,
            item_name: '',
            ecsite_code: '',
            item_id: '',
            barcode_number: '',
            genre_code: 0,
            prefecture_code: '',
            pref_no: '',
            memo: '',
            image_url: '',
            longitude: -1,
            latitude: -1,
            registered_date: '',
            registered_year: -1,
            registered_month: -1 },
    /**
     * ブックマークテーブル作成SQL
     * @return string
     */
    createBookmarksSQL: function(){
        return 'CREATE TABLE IF NOT EXISTS bookmarks ('+
            'id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ' + 
            'status INTEGER, ' + /* 0: scaned, 1: bookmarked; 2: scaned + bookmarked */
            'item_name TEXT, ' +
            'ecsite_code TEXT, ' +
            'item_id TEXT, ' +
            'barcode_number TEXT,' + 
            'genre_code INTEGER,' + 
            'prefecture_code TEXT,' + 
            'pref_no TEXT,' + 
            'memo TEXT,' + 
            'image_url TEXT,' + 
            'longitude REAL, ' + 
            'latitude REAL,' + 
            'registered_date TEXT, '  + 
            'registered_year INTEGER, ' + 
            'registered_month INTEGER ' +
        ')';
    },
    
    loadTopBookmarkListQuery : function() {
        return  'SELECT bookmarks.id, bookmarks.item_name, bookmarks.registered_date, bookmarks.image_url, bookmarks.barcode_number, genres.name, bookmarks.status ' +
                'FROM bookmarks, genres ' +
                'WHERE bookmarks.genre_code = genres.code AND bookmarks.status != "0" ' +
                'ORDER BY bookmarks.registered_date DESC, bookmarks.id DESC '+
                'LIMIT ' + Util.prototype.maxBookmarkNumber + ' OFFSET ';
    },
    
    loadTopScanQuery: function() {
        return  'SELECT bookmarks.id, bookmarks.item_name, bookmarks.registered_date, bookmarks.image_url, bookmarks.barcode_number, genres.name, bookmarks.status ' +
                'FROM bookmarks, genres ' +
                'WHERE bookmarks.genre_code = genres.code AND bookmarks.status IN ("0", "2") ' +
                'ORDER BY bookmarks.registered_date DESC, bookmarks.id DESC '+
                'LIMIT ' + Util.prototype.maxBookmarkNumber + ' OFFSET ';
    },
    
    loadRelativeItemSearchQuery: function(keyword) {
        var splitWord = keyword.split("　"),
            condition = '';
        for(var pname in splitWord){
            condition += ' AND (bookmarks.item_name LIKE "%'+splitWord[pname]+'%" OR bookmarks.memo LIKE "%'+splitWord[pname]+'%") ';
        }    
        return 'SELECT bookmarks.id, bookmarks.item_name, bookmarks.registered_date, bookmarks.image_url, bookmarks.barcode_number, genres.name, bookmarks.status ' +
                'FROM bookmarks, genres ' +
                'WHERE bookmarks.genre_code = genres.code AND bookmarks.status != "0" ' + condition +
                'ORDER BY bookmarks.registered_date DESC, bookmarks.id DESC '+
                'LIMIT ' + Util.prototype.maxBookmarkNumber + ' OFFSET ';  
    },
    
    loadCityBookmarkQuery: function(cityCode) {  
        return 'SELECT bookmarks.id, bookmarks.item_name, bookmarks.registered_date, bookmarks.image_url, bookmarks.barcode_number, genres.name, bookmarks.status ' +
                'FROM bookmarks, genres ' +
                'WHERE bookmarks.genre_code = genres.code AND bookmarks.status != "0" '+
                'AND (bookmarks.prefecture_code = "' + cityCode + '" OR bookmarks.pref_no = "' + cityCode + '") ' +
                'ORDER BY bookmarks.registered_date DESC, bookmarks.id DESC '+
                'LIMIT ' + Util.prototype.maxBookmarkNumber + ' OFFSET ';
    },
    
    countProductbyRegionQuery: function(regionListName) {
        var regionCondition = ' (';
        for (var i = regionListName.length - 1; i >= 0; i--) {
            regionCondition += 'prefectures.pref_name = "' + regionListName[i];
            if (i > 0) {
                regionCondition += '" OR ';
            }
        }
        regionCondition += '") ';
        return 'SELECT   prefectures.pref_name, prefectures.pref_no AS code,COUNT(bookmarks.pref_no) AS prefkazu '+ 
               'FROM     prefectures, bookmarks '+ 
               'WHERE    prefectures.code = bookmarks.prefecture_code AND bookmarks.status != "0" '+
               '         AND prefectures.pref_no = bookmarks.pref_no '+
               '         AND ' + regionCondition +
               'GROUP BY bookmarks.pref_no';
    },
    
    countProductbyPrefQuery: function(prefName) {
        return 'SELECT   prefectures.city_name, COUNT(prefectures.code) AS prefkazu '+ 
               'FROM     prefectures, bookmarks '+ 
               'WHERE    prefectures.code = bookmarks.prefecture_code AND bookmarks.status != "0" '+
               '         AND prefectures.pref_name = "' + prefName + '" ' +
               '         AND bookmarks.status != "0" ' +
               'GROUP BY prefectures.code';
    },
    
    loadProductbyPrefQuery: function(prefName) {
        return 'SELECT   bookmarks.latitude AS lat, bookmarks.longitude AS lng '+ 
               'FROM     prefectures, bookmarks '+ 
               'WHERE    prefectures.code = bookmarks.prefecture_code AND bookmarks.status != "0" '+
               '         AND prefectures.pref_no = bookmarks.pref_no '+
               '         AND (prefectures.pref_name = "' + prefName + '" OR prefectures.city_name = "' + prefName + '")';
    },
    
    loadProductbyDiffPrefQuery: function(prefName) {
        return 'SELECT   prefectures.city_name, bookmarks.latitude AS lat, bookmarks.longitude AS lng '+ 
               'FROM     prefectures, bookmarks '+ 
               'WHERE    prefectures.code = bookmarks.prefecture_code AND bookmarks.status != "0" '+
               '         AND prefectures.pref_no = bookmarks.pref_no '+
               '         AND (prefectures.pref_name <> "' + prefName + '" AND prefectures.city_name <> "' + prefName + '")';
    },
    
    countProductByMonthQuery: function(){
        return 'SELECT   bookmarks.registered_year, bookmarks.registered_month, '+
               '         COUNT(bookmarks.registered_month) AS tuki ' + 
               'FROM     bookmarks '+
               'WHERE    bookmarks.status != "0" '+
               'GROUP BY bookmarks.registered_year, bookmarks.registered_month '+
               'ORDER BY bookmarks.registered_year DESC, bookmarks.registered_month DESC';
    },
    
    countByKeywordQuery: function(){
        return 'SELECT   keywords.id, keywords.name, '+
               '         COUNT(bookmarks_keywords.keyword_id) AS keykazu '+
               'FROM     bookmarks, keywords, bookmarks_keywords '+
               'WHERE    keywords.id = bookmarks_keywords.keyword_id '+
               '         AND bookmarks_keywords.bookmark_id = bookmarks.id '+
               '         AND bookmarks.status != "0" '+
               'GROUP BY bookmarks_keywords.keyword_id '+
               'ORDER BY keykazu DESC ';
    },
    
    countByGenreQuery: function(){
        return 'SELECT   bookmarks.genre_code, genres.name, '+
               '         COUNT(bookmarks.genre_code) AS genrekazu '+ 
               'FROM     bookmarks, genres '+ 
               'WHERE    bookmarks.genre_code = genres.code '+
               '         AND bookmarks.status != "0" '+
               '         AND genres.ecsite_code = "-1" '+
               'GROUP BY bookmarks.genre_code '+
               'ORDER BY bookmarks.genre_code';
    },
    
    countByPrefQuery: function(){
        return 'SELECT   prefectures.code, prefectures.pref_no, prefectures.pref_name, '+
               '         COUNT(bookmarks.pref_no) AS prefkazu '+ 
               'FROM     prefectures, bookmarks '+ 
               'WHERE    prefectures.city_name = "" '+
               '         AND prefectures.pref_no = bookmarks.pref_no '+
               '         AND bookmarks.status != "0" '+
               'GROUP BY bookmarks.pref_no';
    },
    
    countByCityQuery: function(txtPref){
        var sql = 'SELECT   prefectures.code, prefectures.pref_no, prefectures.pref_name, prefectures.city_name, '+
                  '         COUNT(prefectures.code) AS citykazu '+
                  'FROM     prefectures, bookmarks '+
                  'WHERE    prefectures.pref_name = "'+txtPref+'" '+
                  '         AND (prefectures.code = bookmarks.prefecture_code '+
                  '         OR prefectures.city_name = "" AND prefectures.pref_no = bookmarks.pref_no) '+
                  '         AND bookmarks.status != "0" '+
                  'GROUP BY prefectures.code';
        return sql;
    },
    
    loadProductList : function(query, pageNo, success, error) {
        var db = Connection.prototype.openDb();
        var querySql = query +  (pageNo * Util.prototype.maxBookmarkNumber);
        db.transaction(function(tr) {
            tr.executeSql(querySql, [], success, error);
        });
    },
    
    countProductbyPref: function(listCity, index, prefName, callback) {
        var db = Connection.prototype.openDb();            
        db.transaction(function(tx){
            var query = BookmarksService.prototype.countProductbyPrefQuery(prefName);
            tx.executeSql(query, [], function(tx, results){                
                var listProdData = [];
                for (var i = 0, j = results.rows.length; i < j; i ++) {
                    var item = results.rows.item(i);
                    listProdData.push(item.city_name, item.prefkazu);
                }
                callback(listCity, index, listProdData);            
            }, BookmarksService.prototype.errorCallBack);
        },BookmarksService.prototype.errorCallBack,function(){});
    },
    
    countProductbyRegion: function(regionListName, regionIndex, callback) {
        var db = Connection.prototype.openDb();            
        db.transaction(function(tx){
            var query = BookmarksService.prototype.countProductbyRegionQuery(regionListName);
            tx.executeSql(query, [], function(tx, results){
                callback(results, regionIndex);                
            }, BookmarksService.prototype.errorCallBack);
        },BookmarksService.prototype.errorCallBack,function(){});
    },
    
    loadProductbyPref: function(prefName, callback) {
        var db = Connection.prototype.openDb();            
        db.transaction(function(tx){
            var query = BookmarksService.prototype.loadProductbyPrefQuery(prefName);
//            console.log("query: " + query);
            tx.executeSql(query, [], function(tx, results){
                callback(results);            
            }, BookmarksService.prototype.errorCallBack);
        },BookmarksService.prototype.errorCallBack,function(){});
    },
    
    loadProductbyDiffPref: function(prefName, callback) {
        var db = Connection.prototype.openDb();            
        db.transaction(function(tx){
            var query = BookmarksService.prototype.loadProductbyDiffPrefQuery(prefName);
//            console.log("query: " + query);
            tx.executeSql(query, [], function(tx, results){
                callback(results);            
            }, BookmarksService.prototype.errorCallBack);
        },BookmarksService.prototype.errorCallBack,function(){});
    },

    /**
     * @author : 小林
     * @description : 月別ブックマーク数取得
     * @return string
     */
    countProductByMonth: function(callback){
        var db = Connection.prototype.openDb();
        db.transaction(function(tx){
            var query = BookmarksService.prototype.countProductByMonthQuery();
//            console.log("query: " + query);
            tx.executeSql(query, [], function(tx, results){
                callback(results);                
            }, BookmarksService.prototype.errorCallBack);
        },BookmarksService.prototype.errorCallBack,function(){});
    },
    
    /**
     * @author : 小林
     * @description : キーワード別ブックマーク数取得
     * @return string
     */
    countProductByKeyword: function(callback){
        var db = Connection.prototype.openDb();
        db.transaction(function(tx){
            var query = BookmarksService.prototype.countByKeywordQuery();
//            console.log("query: " + query);
            tx.executeSql(query, [], function(tx, results){
                callback(results);                
            }, BookmarksService.prototype.errorCallBack);
        },BookmarksService.prototype.errorCallBack,function(){});
    },
    
    /**
     * @author : 小林
     * @description : ジャンル別ブックマーク数取得
     * @return string
     */
    countProductByGenre: function(callback){
        var db = Connection.prototype.openDb();
        db.transaction(function(tx){
            var query = BookmarksService.prototype.countByGenreQuery();
//            console.log("query: " + query);
            tx.executeSql(query, [], function(tx, results){
                callback(results);                
            }, BookmarksService.prototype.errorCallBack);
        },BookmarksService.prototype.errorCallBack,function(){});
    },
    
    /**
     * @author : 小林
     * @description : 都道府県別ブックマーク数取得
     */
    countProductByAllPref: function(callback){
        var db = Connection.prototype.openDb();
        db.transaction(function(tx){
            var query = BookmarksService.prototype.countByPrefQuery();
//            console.log("query: " + query);
            tx.executeSql(query, [], function(tx, results){
                callback(results);                
            }, BookmarksService.prototype.errorCallBack);
        },BookmarksService.prototype.errorCallBack,function(){});
    },

    getHasProductByCity: function(prefNo, callback){
        var db = Connection.prototype.openDb();
        db.transaction(function(tx){
            var query = 'SELECT DISTINCT prefectures.code, prefectures.city_name FROM prefectures, bookmarks WHERE prefectures.pref_no = "'+prefNo+
                        '" AND (prefectures.code = bookmarks.prefecture_code AND prefectures.pref_no = bookmarks.pref_no) AND bookmarks.status != "0"';
//            console.log("query: " + query);
            tx.executeSql(query, [], function(tx, results){
                callback(results);                
            }, BookmarksService.prototype.errorCallBack);
        },BookmarksService.prototype.errorCallBack,function(){});
    },
    
    /**
     * @author : 小林
     * @description : 市町村別ブックマーク数取得
     */
    countProductByCity: function(prefName, callback){
        var db = Connection.prototype.openDb();
        db.transaction(function(tx){
            var query = BookmarksService.prototype.countByCityQuery(prefName);
           // console.log("query: " + query);
            tx.executeSql(query, [], function(tx, results){
                callback(results);                
            }, BookmarksService.prototype.errorCallBack);
        },BookmarksService.prototype.errorCallBack,function(){});
    },

    getConditionBookmarksSQL: function(registered, params) {
        var joken = '',
            bookmarks_keywords = 'false';
                    
        if(typeof params.ym !== "undefined"){
            //月別リストをタップされた時に追加する条件
            var objYear = params.ym.slice(6,10),
                objMonth = params.ym.slice(10);
                            
            joken = 'bookmarks.registered_year = "'+objYear+'" AND bookmarks.registered_month = "'+objMonth+'"';
        
        } else if(typeof params.genreCode !== "undefined"){
            //ジャンルリストをタップされた時に追加する条件
            joken = 'bookmarks.genre_code = "'+params.genreCode+'"';
        
        } else if(typeof params.keyword !== "undefined"){
            //キーワードリストをタップクされた時に追加する条件
            bookmarks_keywords = 'true';
            joken = 'bookmarks_keywords.keyword_id = "'+params.keyword+'"';
            
        } else if(typeof params.cityCode !== "undefined"){
            //市町村リストをタップされた時に追加する条件
            joken = '(bookmarks.prefecture_code = "'+params.cityCode+'" OR bookmarks.pref_no = "'+params.cityCode+'")';
        
        } else if (typeof params.LatLon !== "undefined") {
            // グーグルマップのクラスターの条件
            var lat = 'bookmarks.latitude IN(';
            var lon = 'bookmarks.longitude IN(';
            for (var i = params.LatLon.length - 1; i >= 0; i --) {                
                var curLatLon = params.LatLon[i];
                lat += curLatLon.lat;
                lon += curLatLon.lng;
                if (i != 0) {
                   lat += ', ';
                   lon += ', ';
                }
            }
            joken = lat + ') AND ' + lon + ')';
        } else if(typeof params.wordsearch !== "undefined"){
            //詳細検索ボタンをタップされた時に追加する条件
            var objWord = params.wordsearch,
                objFromYMD = params.fromymdsearch,
                objToYMD = params.toymdsearch,
                objGenre = params.genresearch,
                objKeyword = params.keywordsearch,
                objPref = params.prefsearch,
                objCity = params.citysearch;
                
                var checkSql = 0;
            //フリーワードの条件
            if (objWord !== ""){
                var splitWord = objWord.split("　");
                for(var pname in splitWord){
                    // if(pname == 0){
                    //     joken += ' (bookmarks.item_name LIKE "%'+splitWord[pname]+'%" OR bookmarks.memo LIKE "%'+splitWord[pname]+'%") ';
                    // }else {
                    //     joken += ' AND (bookmarks.item_name LIKE "%'+splitWord[pname]+'%" OR bookmarks.memo LIKE "%'+splitWord[pname]+'%") ';
                    // }
                    if(pname == 0){
                        joken = ' bookmarks.item_name LIKE "%'+splitWord[pname]+'%" ';
                    }else {
                        joken += ' AND bookmarks.item_name LIKE "%'+splitWord[pname]+'%" ';
                    }
                }
                checkSql = 1;
            }
            
            //日付の条件
            if (objFromYMD !== ""){
                var year = objFromYMD.slice(0,4),
                    month = objFromYMD.slice(5,7),
                    date = objFromYMD.slice(8,10),
                    objFromYMD = year + month + date; //スラッシュを除いた年月日
                    if(checkSql == 1) {
                        joken += ' AND ';
                    } else {
                        checkSql = 1;
                    }
                    joken += ' bookmarks.registered_date >= '+objFromYMD;
            }
            
            if (objToYMD !== ""){
                var year = objToYMD.slice(0,4),
                    month = objToYMD.slice(5,7),
                    date = objToYMD.slice(8,10),
                    objToYMD = year + month + date; //スラッシュを除いた年月日
                    if(checkSql == 1) {
                        joken += ' AND ';
                    } else {
                        checkSql = 1;
                    }
                    joken += ' bookmarks.registered_date <= '+objToYMD;
            }
                
            //ジャンルの条件
            if (objGenre !== ""){
                // joken += (objFromYMD == "") ? '(bookmarks.genre_code = "'+objGenre+'")' : ' AND  (bookmarks.genre_code = "'+objGenre+'")';
                if(checkSql == 1) {
                    joken += ' AND ';
                } else {
                    checkSql = 1;
                }
                joken += '(bookmarks.genre_code = "'+objGenre+'")';
            }
            
            //キーワードの条件
            //if (objKeyword !== null){
            if (objKeyword !== ""){
                bookmarks_keywords = 'true';
                if(checkSql == 1) {
                    joken += ' AND ';
                } else {
                    checkSql = 1;
                }
                joken += ' (bookmarks_keywords.keyword_id = "'+objKeyword+'")';
            }
        
            //都道府県の条件
            if (objPref !== ""){
                if(checkSql == 1) {
                    joken += ' AND ';
                } else {
                    checkSql = 1;
                }
                joken += ' (bookmarks.pref_no = "'+objPref+'")';
            }
            if (objCity !== ""){
                if(checkSql == 1) {
                    joken += ' AND ';
                } else {
                    checkSql = 1;
                }
                joken += ' (bookmarks.prefecture_code = "'+objCity+'")';
            }
        
        // 商品検索をタップされた時に追加する条件
        } else if(typeof params.itemsearch !== "undefined"){
            var objWord = params.itemsearch;
            var splitWord = objWord.split("　");
                for(var pname in splitWord){
                    if(pname == 0){
                        joken += ' (bookmarks.item_name LIKE "%'+splitWord[pname]+'%" OR bookmarks.memo LIKE "%'+splitWord[pname]+'%") ';
                    }else {
                        joken += ' AND (bookmarks.item_name LIKE "%'+splitWord[pname]+'%" bookmarks.memo LIKE "%'+splitWord[pname]+'%") ';
                    }
                }
            
        } else {
            //条件がない時
            joken = ' 1=1 '; 
        }

        return BookmarksService.prototype.findBookmarksSQL(joken, registered, bookmarks_keywords);
    },
    
    /**
     * @author: 小林
     * @description : bookmarksテーブル検索SQL
     * @param joken string 追加条件
     * @param registered boolean ブックマーク済みを検索するかどうか
     * @param offsetNumber ブックマークリスト取得開始する行番号
     * @return string
     */
    findBookmarksSQL: function(joken, registered, bookmarks_keywords){
        var limit =  Util.prototype.maxBookmarkNumber;
        
        var sql = 'SELECT bookmarks.item_name, bookmarks.registered_date, bookmarks.image_url, bookmarks.id, bookmarks.barcode_number, genres.name, bookmarks.status '+
                    ' FROM bookmarks, genres ';
        if (bookmarks_keywords == "true"){
            sql += ' , bookmarks_keywords ';
        }       
            sql += 'WHERE  ' + joken + ' AND genres.code = bookmarks.genre_code ';
        if (registered == "true"){
            sql += ' AND bookmarks.status != "0" ';
        } else if(registered == "false"){
            // sql += ' AND bookmarks.barcode_number NOT IN("", "undefined") ';
            sql += ' AND bookmarks.barcode_number <> "" ';
        }
        if (bookmarks_keywords == "true"){
            sql += ' AND bookmarks_keywords.bookmark_id = bookmarks.id '+
                   ' GROUP BY bookmarks_keywords.bookmark_id ';
        } 
        sql += ' ORDER BY bookmarks.registered_date DESC, bookmarks.id DESC '+
               ' LIMIT '+limit+' OFFSET ';
         // console.log("query: " + sql);
        return sql;
    },
    
    createBookmarkSearch: function(genresCallback, prefCallback, keyCallback){
        var db = Connection.prototype.openDb();
        db.transaction(function(tx) {
            // genres
            tx.executeSql(BookmarksService.prototype.countByGenreQuery(), [], function(tx, results){
                genresCallback(results);                
            }, BookmarksService.prototype.errorCallBack);
            // pref
            tx.executeSql(BookmarksService.prototype.countByPrefQuery(), [], function(tx, results){
                prefCallback(results);
            }, BookmarksService.prototype.errorCallBack);
            // keyword
            tx.executeSql(BookmarksService.prototype.countByKeywordQuery(), [], function(tx, results){
                keyCallback(results);
            }, BookmarksService.prototype.errorCallBack);
        },  BookmarksService.prototype.errorCallBack,function(){  Util.prototype.hideSpinner(); });
    },
    
    deleteAdvanceBookmark: function(deleteIds, updatedItems, callback) {        
        var db = Connection.prototype.openDb();
        db.transaction(function(tx){
            if (deleteIds != null && deleteIds.length > 0) {
                tx.executeSql(BookmarksService.prototype.deleteBookmarkQuery(deleteIds), []);
            }
            if (typeof updatedItems != 'undefined' && updatedItems != null) {
                for (var i = updatedItems.length - 1; i >= 0; i --) {
                    var updateObj = BookmarksService.prototype.updateBookmarkQuery(updatedItems[i]);
                    tx.executeSql(updateObj.query, updateObj.values);
                }
            }
        }, BookmarksService.prototype.errorCallBack, callback);
        
        var ids = '';
        if (deleteIds != null && deleteIds.length > 0) {
            ids = deleteIds.toString();
        }
        if (updatedItems != null) {            
            for (var i = updatedItems.length - 1; i >= 0; i --) {
                if (ids != '') {
                    ids += ',';
                }
                ids += updatedItems[i].id;
            }
        }
        
        if (ids != '') {
            DetailProductService.prototype.saveLog({log_type: DetailProductService.prototype.SERVER_LOG_DELETE, bookmarkId: ids});
        }
    },
    
    deleteBookmarkQuery: function(deleteIds) {
        var query = 'DELETE FROM bookmarks WHERE';
        for(var i = deleteIds.length - 1; i >= 0; i--) {
            query += ' id="' + deleteIds[i] + '"';
            if (i > 0) {
                query += ' OR';
            }
        }
        return query;
    },
    
    insertBookmarkQuery: function(item) {
        var query = 'INSERT INTO bookmarks (',
            queryValues = ' VALUES (', nonComma = true;
        var defaultValues = BookmarksService.prototype.DEFAULT_VALUES;
        var values = [];
        for (var key in defaultValues) {
            var value = (typeof item[key] != 'undefined') ? item[key] : defaultValues[key];
            if (typeof value != 'undefined') {
                values.push(value);
                query += nonComma ? key : (',' + key);
                queryValues += nonComma ? '?' : ',?';
                
                if (nonComma) {
                    nonComma = false;                
                }
            }
        }
        queryValues += ')';
        query += ' )' + queryValues;
        
        // console.log('insertBookmarkQuery: ' + JSON.stringify({query: query, values: values}));
        return {query: query, values: values};
    },
    
    updateBookmarkQuery: function(item, bmid) {
        var query = 'UPDATE bookmarks SET ',
            nonComma = true,
            id = (typeof item.id != 'undefined') ? item.id : bmid;        
        var values = [];
        for (var key in item) {
            if (key == 'id' || (typeof BookmarksService.prototype.DEFAULT_VALUES[key] == 'undefined')) {
                continue;
            }
            values.push(item[key]);
            query += nonComma ? key : (',' + key);
            query += '=?';
            if (nonComma) {
                nonComma = false;                
            }
        }
        query += ' WHERE id=' + id;
        
        // console.log('updateBookmarkQuery: ' + JSON.stringify({query: query, values: values}));
        return {query: query, values: values};
    },
    
    /**
     * @author: Huy
     * @description: ブックマーク登録・編集のデータをロードする
     * @params: 
     *          id: bookmark id
     *          check: true -> load prefectures
     *          callback: callback
     * @return string
     */
    loadBookmarkDataByID: function(id, check, callback) {
        var db = Connection.prototype.openDb();
        db.transaction(function(tx){
            var query = BookmarksService.prototype.loadBookmarkDataByIDQuery(id, check);
            tx.executeSql(query, [], function(tx, results){
                callback(results);                
            }, BookmarksService.prototype.errorCallBack);
        }, BookmarksService.prototype.errorCallBack,function(){});     
    }, 
    
    loadBookmarkDataByIDQuery: function(id, check) {
        return check ? 
                'SELECT bookmarks.status, bookmarks.item_name, bookmarks.memo, bookmarks.genre_code, genres.name AS genname, bookmarks.prefecture_code, bookmarks.pref_no, prefectures.pref_name, prefectures.city_name, ' +
                'bookmarks.ecsite_code, bookmarks.item_id, bookmarks.barcode_number, bookmarks.image_url, bookmarks.longitude, bookmarks.latitude, bookmarks.registered_date, bookmarks.registered_year, bookmarks.registered_month ' +
                'FROM bookmarks, genres, prefectures ' +
                'WHERE bookmarks.genre_code = genres.code AND bookmarks.prefecture_code = prefectures.code AND bookmarks.pref_no = prefectures.pref_no ' +
                'AND bookmarks.id = ' + id 
               : 
                'SELECT bookmarks.status, bookmarks.item_name, bookmarks.memo, bookmarks.genre_code, genres.name AS genname, ' +
                'bookmarks.ecsite_code, bookmarks.item_id, bookmarks.barcode_number, bookmarks.image_url, bookmarks.registered_date, bookmarks.registered_year, bookmarks.registered_month ' +
                'FROM bookmarks, genres ' +
                'WHERE bookmarks.genre_code = genres.code AND bookmarks.id = ' + id;
    },
    
    /*
     * Save bookmark and its related keyword.
     */
    saveBookmark: function(params, callback) {
        var queryObj = (typeof params.id == 'undefined') ? BookmarksService.prototype.insertBookmarkQuery(params) : 
                                                        BookmarksService.prototype.updateBookmarkQuery(params);        
        
        var db = Connection.prototype.openDb();
        db.transaction(function(tx){
            tx.executeSql(queryObj.query, queryObj.values, function(tx, results){                                
                if (typeof params.id == 'undefined') {
                    BookmarksService.prototype.saveBookmarkRelation(tx, results.insertId, params);
                }
                
                if(typeof params.isBookmarked != 'undefined' && !params.isBookmarked) {
                    var bmId = (typeof params.id == 'undefined') ? results.insertId : params.id;
                    DetailProductService.prototype.saveLog(DetailProductService.prototype.SERVER_LOG_BOOKMARK, bmId, params);
                }
            }, BookmarksService.prototype.errorCallBack);
            
            if (typeof params.id != 'undefined') {
                BookmarksService.prototype.saveBookmarkRelation(tx, params.id, params);
            }
            
        }, BookmarksService.prototype.errorCallBack, callback);
        
      
     
    },
    
    saveBookmarkRelation: function(tx, bmid, params) {
        if (typeof params.newKeyword != 'undefined') {
            for (var i = 0; i < params.newKeyword.length; i ++) {
                tx.executeSql(KeywordsService.prototype.getAddKeywordSql(), [params.newKeyword[i]], 
                    function(tx, kw_result){
                        // add new relation here
                        tx.executeSql(Bookmarks_keywordsService.prototype.getAddKeysSql(), [bmid, kw_result.insertId]);    
                }, BookmarksService.prototype.errorCallBack);             
            }
        }
        if (typeof params.newRelation != 'undefined') {
            for (var i = 0; i < params.newRelation.length; i ++) {
                tx.executeSql(Bookmarks_keywordsService.prototype.getAddKeysSql(), [bmid, params.newRelation[i]]);
            }
        }   
        if (typeof params.oldKeyword != 'undefined') {
            var deleteQuery = Bookmarks_keywordsService.prototype.getDeleteKeysSql(bmid, params.oldKeyword);
            tx.executeSql(deleteQuery, []);
        }
    },
    
    queryExecuteSql : function(querySql,callback,error){
        var db = Connection.prototype.openDb();
        db.transaction(
            function(tr){
                tr.executeSql(querySql,[],function(tr,result){
                    callback(result);
            }, error);      
        });      
    },

    saveScanBookmark : function(bmid, params, success, error) { 
        var db = Connection.prototype.openDb();
        var isUpdate = (typeof bmid != 'undefined' && bmid != '');
        var queryObj = isUpdate ? BookmarksService.prototype.updateBookmarkQuery(params, bmid) : 
                                BookmarksService.prototype.insertBookmarkQuery(params);
        db.transaction(
        function(tr){
            tr.executeSql(queryObj.query, queryObj.values, function(tr,bm_result){
                var sbmId = isUpdate ? bmid : bm_result.insertId;
                if(jQuery.isFunction(success)) {
                    success(sbmId);
                }
                
                if (params.saveLogType && !isUpdate) { // can check scan or bookmark
                    DetailProductService.prototype.saveLog(params.saveLogType, sbmId, params);
                }
            }, function(tr, bm_result) {              
               if(jQuery.isFunction(error)) {
                   error();
               }
            });                     
        });
        
        if(params.saveLogType && isUpdate) {
            BookmarksService.prototype.loadBookmarkDataByID(bmid, true, function(results) {
                if (results.rows.length > 0) {
                    DetailProductService.prototype.saveLog(params.saveLogType, bmid, results.rows.item(0));
                } else {
                    BookmarksService.prototype.loadBookmarkDataByID(bmid, false, function(results) {
                        if (results.rows.length > 0) {
                            DetailProductService.prototype.saveLog(params.saveLogType, bmid, results.rows.item(0));
                        }
                    });
                }
            });            
        }
    },
    
    // used when bookmark bookmark search data
    // saveSearchBookmark : function(params, success, error) { 
    //     var db = Connection.prototype.openDb();
    //     var queryObj = BookmarksService.prototype.insertBookmarkQuery(params);
    //     db.transaction(
    //     function(tr){
    //         tr.executeSql(queryObj.query, queryObj.values, function(tr,bm_result){
    //             var data = BookmarksService.prototype.getLogData(params.saveLogType, params);
    //             data.bookmarkId = bm_result.insertId;
    //             DetailProductService.prototype.saveLog(data);
    //             if(jQuery.isFunction(success)) {
    //                 success(bm_result.insertId);
    //             }
    //         }, function(tr, bm_result) {              
    //            if(jQuery.isFunction(error)) {
    //                error();
    //            }
    //         });                     
    //     });
    // },

    // updateBmScan : function(bmId, status, success, error) {
    //     var db = Connection.prototype.openDb();
    //     var query = 'UPDATE bookmarks SET status=? WHERE id=' + bmId;
    //     db.transaction(
    //     function(tr){
    //         tr.executeSql(query, [status], function(tr, bm_result){
    //             if(jQuery.isFunction(success)) {
    //                 success(bm_result);
    //             }
    //         }, function(tr, bm_result) {
    //            if(jQuery.isFunction(error)) {
    //                error();
    //            }
    //         });                     
    //     });
    // },
    
    getBookmarkObject : function(bookmark_id,callback){
        var sqlQuery = "SELECT * FROM bookmarks WHERE id = '" + bookmark_id + "'";
        BookmarksService.prototype.queryExecuteSql(sqlQuery,callback);
    },
    
    getBookmarkByECsiteCodeAndItemId : function(ec_code, item_id, callback, error) {
        var sqlQuery = "SELECT * FROM bookmarks WHERE ecsite_code='" + ec_code +"' and item_id='" + item_id + "'" ;
        BookmarksService.prototype.queryExecuteSql(sqlQuery, callback, error);
    },
    
    getBookmarkIDByECsiteCodeAndItemId : function(ec_code, item_id, data, callback, error) {
        var querySql = "SELECT id FROM bookmarks WHERE ecsite_code='" + ec_code +"' and item_id='" + item_id + "'" ;
        var db = Connection.prototype.openDb();
        db.transaction(
            function(tr){
                tr.executeSql(querySql,[],function(tr,result){
                    callback(data, result);
            }, error);      
        });  
    },
    
    getBmDataByBarcode : function(barcode, callback){ //checkIsExitBookmark
        var sqlQuery = "SELECT * FROM bookmarks WHERE barcode_number='" + barcode + "'";
        
        var db = Connection.prototype.openDb();
        db.transaction(function(tx){
//            console.log("query: " + sqlQuery);
            tx.executeSql(sqlQuery, [], function(tx, results){
                callback(results);                
            }, BookmarksService.prototype.errorCallBack);
        }, BookmarksService.prototype.errorCallBack,function(){});     
    },
    
    errorCallBack: function(err){
        console.log("bookmarks検索中にエラーが発生: " + err.code + "; "  + JSON.stringify(err));
    },
   
};
